﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.OracleClient;
using System.Data.Common;

namespace ChBusiness.FlowOperation
{
    /// <summary>
    /// 案例信息操作
    /// </summary>
    public class SerialInstanceOperation
    {
        Database dbGloab = BasicOperate.CreateNetOfficeDataBase();

        /// <summary>
        /// 获取案例信息
        /// </summary>
        /// <param name="strIid">案件编号</param>
        /// <param name="strName">申请人姓名</param>
        /// <param name="strWid">流程id</param>
        /// <returns></returns>
        public DataTable GetSerialInfo(string strIid, string strName, string strWid)
        {
            //添加业务删除标识条件 and t.isdelete <>1  addby zhongjian 20091223
            string strSql = "select a.iid,a.name from st_instance a where isdelete <>1";
            if (!string.IsNullOrEmpty(strIid))
            {
                strSql += string.Format(" and iid like '%{0}%'", strIid);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and name like '%{0}%'", strName);
            }
            if (!string.IsNullOrEmpty(strWid))
            {
                strSql += string.Format(" and a.wid='{0}'", strWid);
            }

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 根据flowname获取wid
        /// </summary>
        /// <param name="strWname">The STR wname.</param>
        /// <returns></returns>
        public string GetWidWithWname(string strWname)
        {
            string strReturn = string.Empty;
            string strSql = string.Format("select wid from st_workflow a where rot=0 and wname='{0}'", strWname);

            object objReturn = dbGloab.ExecuteScalar(CommandType.Text, strSql);
            if (objReturn != null)
            {
                strReturn = objReturn.ToString();
            }
            return strReturn;
        }

     
        /// <summary>
        /// 根据iid获取wid
        /// </summary>
        /// <param name="strWid">The STR wid.</param>
        /// <returns></returns>
        public string GetMaxIidByWid(string strWid)
        {
            string strReturn = string.Empty;
            string strSql = string.Format("select max(iid) from st_instance a where wid='{0}'", strWid);

            object objReturn = dbGloab.ExecuteScalar(CommandType.Text, strSql);
            if (objReturn != null)
            {
                strReturn = objReturn.ToString();
            }
            return strReturn;
        }

        /// <summary>
        /// 根据wid获取fid
        /// </summary>
        /// <param name="strFormName">流程名称</param>
        /// <returns></returns>
        public string GetFirstFidByWname(string strFormName)
        {
            string strReturn = string.Empty;
            string strSql = string.Format(@"select fid
                  from st_form a where a.rot=0
                   and a.fname like '%{0}%'", strFormName);

            object objReturn = dbGloab.ExecuteScalar(CommandType.Text, strSql);
            if (objReturn != null)
            {
                strReturn = objReturn.ToString();
            }
            return strReturn;
        }



        /// <summary>
        /// 获取最新的五条行政许可信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetResultInstanceInfo()
        {
            //添加业务删除标识条件 and a.isdelete <>1  addby zhongjian 20091223
            string strSql = @"select * from (select a.iid 案件编号,
                           a.name 申请人,
                           (case
                             when a.status = 1 then
                              '正在办理'
                             when a.status = 2 then
                              '审批通过'
                             when a.status = -2 then
                              '审批未通过'
                           end) 审核状态
                            from st_instance a where (a.status=2 or a.status=-2) and a.isdelete <>1 order by a.iid desc) where rownum<=5";
            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// Gets the instance result info.(add by lj)
        /// </summary>
        /// <param name="strIid">The STR iid.</param>
        /// <param name="strRequestStartDate">The STR request start date.</param>
        /// <param name="strRequestEndDate">The STR request end date.</param>
        /// <param name="strWid">The STR wid.</param>
        /// <param name="strRequester">The STR requester.</param>
        /// <returns></returns>
        public DataTable GetInstanceResultInfo(string strIid, string strRequestStartDate,
            string strRequestEndDate, string strWid)
        {
            //添加业务删除标识条件 and i.isdelete <>1  addby zhongjian 20091223
            string strSql = @"select iid 受理编号,
                           name 申请单位,
                           (case
                             when status = 1 then
                              '正在办理'
                             when status = 2 then
                              '通过'
                             when status = -2 then
                              '未通过'
                           end) 办件状态,
                            to_char(accepted_time,'yyyy-mm-dd') 申请日期,
                            to_char(exendtime,'yyyy-mm-dd') 办结日期
                            from st_instance a,st_workflow b 
                            where a.wid=b.wid and (status=2 or status=-2) and a.isdelete <>1";
            //to_char(accepted_time,'yyyy-mm-dd') 受理时间
            if (!string.IsNullOrEmpty(strIid))
            {
                strSql += string.Format(" and iid like '%{0}%'", strIid);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            if (!string.IsNullOrEmpty(strWid))
            {
                strSql += string.Format(" and a.wid='{0}'", strWid);
            }
            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 查询政许可信息
        /// </summary>
        /// <param name="strIid">案件编号</param>
        /// <param name="strName">申请人</param>
        /// <param name="strFlow">办理事项</param>
        /// <param name="strRequestStartDate">受理时间(开始)</param>
        /// <param name="strRequestEndDate">受理时间(结束)</param>
        /// <returns></returns>
        /// <!--//添加业务删除标识条件 and a.isdelete <>1  addby zhongjian 20091223-->
        public DataTable GetInstanceInfo(string strIid, string strName, string strFlow, string strRequestStartDate, string strRequestEndDate)
        {
            string strSql = @"select rownum 序号,iid 办件编号,
(select slbh from ut_地图审核受理通知书 a where iid=a.iid and input_index=0 and rownum<2) 受理编号,
                            c.flowtype 受理事项,
                           name 申请单位,
                           (case
                             when status = 1 then
                              '正在办理'
                             when status = 2 then
                              '通过'
                             when status = -2 then
                              '未通过'
                           end) 办件状态,
                            to_char(accepted_time,'yyyy-mm-dd') 申请日期,
                            to_char(exendtime,'yyyy-mm-dd') 应办结日期                           
                            from st_instance a,st_workflow b,xt_workflow_define c
                            where a.wid=b.wid  and a.isdelete <>1
                            and b.wname=c.flowname and c.ispub=1 and c.isdelete=0 ";

            if (!string.IsNullOrEmpty(strIid))
            {
                //strSql += string.Format(" and iid like '%{0}%'", strIid);

                strSql += string.Format(" and iid in (select iid from ut_地图审核受理通知书 a where slbh like '%{0}%')", strIid);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and name like '%{0}%'", strName);
            }
            if (!string.IsNullOrEmpty(strFlow))
            {
                strSql += string.Format(" and flowtype like '%{0}%'", strFlow);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            strSql += " order by accepted_time desc";

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取结果公示信息
        /// </summary>
        /// <param name="strIid">案件编号</param>
        /// <param name="strName">申请人</param>
        /// <param name="strFlow">办理事项</param>
        /// <param name="strRequestStartDate">受理时间(开始)</param>
        /// <param name="strRequestEndDate">受理时间(结束)</param>
        /// <returns></returns>
        /// <!--//添加业务删除标识条件 and a.isdelete <>1  addby zhongjian 20091223-->
        public DataTable GetJggs(string strIid, string strName, string strFlow, string strRequestStartDate, string strRequestEndDate)
        {
            string strSql = @"select t.iid 办件编号,
       m.批准号 审图号,
       name 申请单位,
       图名,
       to_char(finish_time, 'yyyy-mm-dd') 批准日期
  from st_instance t, ut_国家测绘局地图审核申请表 m
 where t.iid = m.iid
   and m.input_index = 0
   and t.status = 2
   and m.批准号 is not null";

            if (!string.IsNullOrEmpty(strIid))
            {
                //strSql += string.Format(" and iid like '%{0}%'", strIid);

                strSql += string.Format(" and m.批准号 like '%{0}%'", strIid);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and name like '%{0}%'", strName);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            strSql += " order by accepted_time desc";

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// Gets the serial info.
        /// </summary>
        /// <param name="strIid">The STR iid.</param>
        /// <param name="strRequestStartDate">The STR request start date.</param>
        /// <param name="strRequestEndDate">The STR request end date.</param>
        /// <param name="strWid">The STR wid.</param>
        /// <param name="strRequester">The STR requester.</param>
        /// <returns></returns>
        public DataTable GetMainSerialInfo(string strIid, string strRequestStartDate,
            string strRequestEndDate, string strWid, string strRequester)
        {
            //添加业务删除标识条件 and a.isdelete <>1  addby zhongjian 20091223
            string strSql = string.Format(@"select rownum 序号,a.iid 案件编号,
                   a.name 申请人,
                   (case
                     when a.status = 1 then
                      '正在办理'
                     when a.status = 2 then
                      '审批通过'
                     when a.status = -2 then
                      '审批未通过'
                   end) 审核状态,
                    (select distinct step from st_work_item b where 
                    b.iid=a.iid and (b.active = 1 or b.active = 0) and rownum<2) 所在岗位
                    from st_instance a where a.iid in (select b.iid from 
                 xt_instance_ext b,xt_submit_info c where b.关联ID=c.iid and c.REQUESTER='{0}') and a.isdelete <>1", strRequester);
            if (!string.IsNullOrEmpty(strIid))
            {
                strSql += string.Format(" and iid like '%{0}%'", strIid);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            if (!string.IsNullOrEmpty(strWid))
            {
                strSql += string.Format(" and a.wid='{0}'", strWid);
            }

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 根据name获取value
        /// </summary>
        /// <param name="strName">The STR name.</param>
        /// <returns></returns>
        public string GetValue(string strName)
        {
            string strReturn = string.Empty;
            string strSql = string.Format(@"select SEED_VALUE from st_number_seed t where SEED_NAME='{0}'", strName);

            object objReturn = dbGloab.ExecuteScalar(CommandType.Text, strSql);
            if (objReturn != null)
            {
                strReturn = objReturn.ToString();
            }
            return strReturn;
        }

        /// <summary>
        /// 修改VALUE
        /// </summary>
        /// <param name="strName">name</param>
        /// <param name="decValue">value</param>
        /// <returns></returns>
        public int UpdateValue(string strName, decimal decValue)
        {
            Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
            string strSql = string.Format(@"update st_number_seed set SEED_VALUE={1} where SEED_NAME='{0}'", strName, decValue);

            return dbTemp.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 获取留言咨询信息
        /// </summary>
        /// <param name="strID">ID</param>
        /// <param name="isPub">是否公布</param>
        /// <param name="iNum">查询条数</param>
        /// <param name="hHidWordLen">隐藏字符</param>
        /// <param name="strType">留言类型</param>
        /// <param name="strTitle">留言主题</param>
        /// <param name="strMsgCode">留言码</param>
        /// <param name="strFromDate">开始日期</param>
        /// <param name="strToDate">结束日期</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091028-->
        public DataTable GetLyzxInfo(string strID, bool isPub, int iNum, int hHidWordLen, string strType, string strTitle, string strMsgCode,
            string strFromDate, string strToDate)
        {
            string strSql = string.Format(@"select * from (select id,can_sync,sync_type,name,email,sex,address,dh,slbm,notes,sj,wtlx,sfgk,
                createdate,msshowtype,
                (case when length(zt)>{0} then Substr(zt,0,{0})|| '...' else zt end ) title,
                (case when wtlx = 1 then '咨询'
                      when wtlx = 2 then '建议'
                      when wtlx = 3 then '投诉'
                      when wtlx = 3 then '求助'
                      when wtlx = 3 then '其它'
                 end) TypeText
                from xt_lyzx where msshowtype=1 ", hHidWordLen);
            if (!string.IsNullOrEmpty(strID))
                strSql += string.Format(" and id='{0}'", strID);
            if (isPub)
            {
                strSql += string.Format(" and sfgk='公开'");//是否公开
                try
                {
                    if (!string.IsNullOrEmpty(strType))//问题类型
                    {
                        int iType = int.Parse(strType);
                        if (iType > 0)
                            strSql += string.Format(" and wtlx='{0}'", strType);
                    }
                }
                catch (Exception ex)
                { }
                if (!string.IsNullOrEmpty(strTitle))//主题
                    strSql += string.Format(" and xt like '%{0}%'", strTitle);
                if (!string.IsNullOrEmpty(strFromDate))//开始日期
                    strSql += string.Format(" and createdate>to_date('{0}','yyyy-mm-dd')", strFromDate);
                if (!string.IsNullOrEmpty(strToDate))//结束日期
                    strSql += string.Format(" and createdate<to_date('{0}','yyyy-mm-dd')", strToDate);
            }
            else//按留言码查询
            {
                if (!string.IsNullOrEmpty(strMsgCode))
                    strSql += string.Format("and sfgk='不公开' and MsgCode='{0}'", strMsgCode);
            }
            strSql += string.Format(" order by createdate desc) ");
            if (iNum > 0)
                strSql += string.Format(" where rownum <= {0}", iNum);

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取留言咨询回复信息
        /// </summary>
        /// <param name="strLYID">留言咨询ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091106-->
        public DataTable GetLyzxFKInfo(string strLYID)
        {
            string strSql = string.Format(@"select a.id,a.fk_notes,fk_date,b.user_name,fk_userid
                            from xt_lyzx_fk a ,st_user b where a.fk_userid=b.userid and  ly_id='{0}' order by fk_date", strLYID);
            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 根据条件获取常见问题信息(st_other)
        /// </summary>
        /// <param name="strID">ID</param>
        /// <param name="strType">信息大类</param>
        /// <param name="strSmaillType">信息小类</param>
        /// <param name="iNum">查询条数</param>
        /// <param name="hHidWordLen">隐藏字符</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091028-->
        public DataTable GetAskQuestion(string strID, string strType, string strSmaillType,
            int iNum, int hHidWordLen)
        {
            string strSql = string.Format(@"select id,(case when length(t.title)>{0} then 
                Substr(t.title,0,{0}) || '...' else t.title end ) as title,content,
                to_char(time,'yyyy-mm-dd') CreateTime,type,is_notice,smalltype,can_sync,sync_type
                    from st_other t where 1=1 ", hHidWordLen);
            if (!string.IsNullOrEmpty(strID))
                strSql += string.Format(" and id='{0}'", strID);
            if (!string.IsNullOrEmpty(strType))
                strSql += string.Format(" and type='{0}'", strType);//信息大类
            if (!string.IsNullOrEmpty(strSmaillType))
                strSql += string.Format(" and smalltype='{0}'", strSmaillType);//信息小类
            if (iNum > 0)
                strSql += string.Format(" and rownum <= {0}", iNum);

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 从数据字典中获取相关数据
        /// </summary>
        /// <param name="strName">数据字典名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091118-->
        public DataTable GetParamsList(string strName)
        {
            string strSql = string.Empty;
            strSql = string.Format("select keyvalue,keycode from sys_params where name='{0}'", strName);
            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取非一级部门信息列表
        /// </summary>
        /// <returns></returns>
        /// <!--addby zhongjian 20091201-->
        public DataTable GetDepart()
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select * from st_department t where departid<>35 
                 start with t.parent_id='0'
                 connect by prior departid=parent_id");
            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取流程扩展信息
        /// </summary>
        /// <param name="strID">流程ID</param>
        /// <param name="strFlowName">流程名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100426-->
        public DataTable GetWorkDefine(string strID, string strFlowName)
        {
            string strSql = string.Format(@"select id,
                                                   flowname,
                                                   flowtype,
                                                   flownum,
                                                   ischeck,
                                                   createtime,
                                                   edittime,
                                                   pubtime,
                                                   ispub,
                                                   isdelete,
                                                   can_sync,
                                                   sync_type,
                                                   issave,
                                                   isshowdialog,
                                                   interfaceurl,
                                                   interfacetype,
                                                   flowview,
                                                   flagview
                                              from xt_workflow_define
                                             where ispub = '1'
                                                   and isdelete = '0'
                                                   and issave = '1'");
            if(!string.IsNullOrEmpty(strID))
                strSql += string.Format(" and id = '{0}'", strID);
            if (!string.IsNullOrEmpty(strFlowName))
                strSql += string.Format(" and flowname = '{0}'", strFlowName);
            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取IID号
        /// </summary>
        /// <param name="wid"></param>
        /// <param name="userId"></param>
        /// <param name="ownerName"></param>
        /// <returns></returns>
        public long GetIIDWithEnterprise(string wid, string userId, string ownerName)
        {
            IDataParameter[] idParms =  new OracleParameter[4];
            idParms[0] = new OracleParameter();
            idParms[0].ParameterName = "wid";
            idParms[0].Value = wid;
            idParms[1] = new OracleParameter();
            idParms[1].ParameterName = "userId";
            idParms[1].Value = userId;
            idParms[2] = new OracleParameter();
            idParms[2].ParameterName = "ownerName";
            idParms[2].Value = ownerName;
            idParms[3] = new OracleParameter();
            idParms[3].ParameterName = "returnValue";
            idParms[3].Direction = ParameterDirection.Output;
            idParms[3].DbType = DbType.Int64;

            DbCommand cmd = dbGloab.GetStoredProcCommand("PublicEvents.GetIIdNumber");
            // 依次把参数传入存储过程
            foreach (IDataParameter parameter in idParms)
            {
                cmd.Parameters.Add(parameter);
            }

            object objReturn = dbGloab.ExecuteScalar(cmd);

            if (idParms[3].Value != null)
            {
                return long.Parse(idParms[3].Value.ToString());
            }
            else
            {
                return 0;
            }
        }

    }
}
